﻿Imports System.Data.SqlClient
Imports CrystalDecisions.CrystalReports.Engine

Public Class frmAccountStatement

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If Me.txtAccName.Text.Trim.Length = 0 Then
            MsgBox("Please select a valid account from the list")
            Exit Sub
        Else
            Try
                Me.Cursor = Cursors.WaitCursor
                Dim dap As New SqlDataAdapter( _
                        "( Select 0 MoveNo,N'Open Balance' Descr,N'' Branch,N'" & Me.txtAccNo.Text.Trim & _
                        "' AccNo,N'" & Me.txtAccName.Text.Trim & "' Acc4,N'' ChNo,0 TotalIn,0 TotalOut" & _
                        ",Sum(TotalOut)-Sum(TotalIn) SNo,N'" & Me.DTPPeriodFrom.Value.ToShortDateString & _
                        "' TransDate,0 Reversed,N'F' Source From Transactions Where AccNo=N'" & Me.txtAccNo.Text.Trim & _
                        "' and TransDate < N'" & _
                        Me.DTPPeriodFrom.Value.ToShortDateString & " 00:00:00') " & _
                        "Union All " & _
                        "(Select MoveNo,Descr,Branch,AccNo,Acc4,ChNo,TotalIn,TotalOut,TotalOut-TotalIn SNo,TransDate,Reversed,Source " & _
                        "From Transactions Where AccNo=N'" & Me.txtAccNo.Text.Trim & "' " & _
                        "and TransDate >= N'" & Me.DTPPeriodFrom.Value.ToShortDateString & " 00:00:00' " & _
                        "and TransDate <= N'" & Me.DTPPeriodTo.Value.ToShortDateString & " 23:59:59')", cnn)
                Dim das As New DataSet

                cnn.Open()
                dap.Fill(das, "Transactions")
                cnn.Close()

                Dim rpt As New StatementOfAccount
                rpt.SetDataSource(das)

                'Create Parameters
                Dim crParameterDiscreteValue As New CrystalDecisions.Shared.ParameterDiscreteValue
                Dim crParameterFieldDefinitions As ParameterFieldDefinitions
                Dim crParameterFieldLocation As ParameterFieldDefinition
                Dim crParameterValues As New CrystalDecisions.Shared.ParameterValues
                crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields

                'MinDate PARAMETER
                crParameterFieldLocation = crParameterFieldDefinitions.Item("MinDate")
                crParameterValues = crParameterFieldLocation.CurrentValues
                crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
                crParameterDiscreteValue.Value = Me.DTPPeriodFrom.Value
                crParameterValues.Add(crParameterDiscreteValue)
                crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

                'MaxDate PARAMETER
                crParameterFieldLocation = crParameterFieldDefinitions.Item("MaxDate")
                crParameterValues = crParameterFieldLocation.CurrentValues
                crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
                crParameterDiscreteValue.Value = Me.DTPPeriodTo.Value
                crParameterValues.Add(crParameterDiscreteValue)
                crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

                Me.CrystalReportViewer1.ReportSource = rpt

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim a As New frmSelectAccount
        a.ShowDialog()

        If SelAccNo <> "" Then
            Me.txtAccNo.Text = SelAccNo
            Me.txtAccName.Text = SelAccName
        End If
    End Sub

    Private Sub txtAccNo_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtAccNo.KeyUp
        If e.KeyCode = Keys.Enter Then
            Me.txtAccName.Text = GetAccName(Me.txtAccNo.Text.Trim)
        End If
    End Sub

    Private Sub txtAccNo_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtAccNo.TextChanged
        Me.txtAccName.Clear()
    End Sub

    Private Sub frmAccountStatement_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized
    End Sub
End Class